using System;
using System.Data;
using System.Collections;
using APP.QUANLY.Common;
using RMAS.DAL;

namespace RMAS.DAL
{
    /// <summary>
    /// Summary description for DM_BRANCH.
     /// </summary>
	public class DM_BRANCHDao
    {
	    private string _SQLSelectByKey;
	    private string _SQLUpdate;
	    private string _SQLInsert;
	    private string _SQLDelete;
	    private string _SQLSelectAll;

        #region Properties

        public IDbConnection _dbConnection;

        public IDbConnection DbConnection
        {
            get { return _dbConnection; }
            set { _dbConnection = value; }
        }

        #endregion
        
        #region Constructor

        public DM_BRANCHDao()
        {
            _dbConnection = StaticVar.ServerConnection; ;
		    InitSqlQuery();
        }

        public DM_BRANCHDao(IDbConnection con)
        {
            _dbConnection = con;
            InitSqlQuery();
        }

        #endregion


        private void ToLowerQuery()
        {
            _SQLDelete = _SQLDelete.ToLower();
            _SQLUpdate = _SQLUpdate.ToLower();
            _SQLInsert = _SQLInsert.ToLower();
            _SQLSelectByKey = _SQLSelectByKey.ToLower();
        }

        private void ToUpperQuery()
        {
            _SQLDelete = _SQLDelete.ToUpper();
            _SQLUpdate = _SQLUpdate.ToUpper();
            _SQLInsert = _SQLInsert.ToUpper();
            _SQLSelectByKey = _SQLSelectByKey.ToUpper();
        }

        #region InitSqlQuery
        private void InitSqlQuery()
       {
            string query;

            query = @"SELECT ID, IDPOS, Branch_Name, Branch_Address, Branch_Phone, Branch_Fax, Branch_Email, Branch_Note, Branch_Logo, Branch_Image, Branch_Director, Branch_Revenue, Branch_Facebook, DateTimeBeginLicence, DateTimeEndLicence, HasLicenceKey, Version, IsUse FROM DM_BRANCH WHERE (ID = @ID)";
	        _SQLSelectByKey = query;

            query = @"UPDATE DM_BRANCH SET Branch_Name = @Branch_Name, Branch_Address = @Branch_Address, Branch_Phone = @Branch_Phone, Branch_Fax = @Branch_Fax, Branch_Email = @Branch_Email, Branch_Note = @Branch_Note, Branch_Logo = @Branch_Logo, Branch_Image = @Branch_Image, Branch_Director = @Branch_Director, Branch_Revenue = @Branch_Revenue, Branch_Facebook = @Branch_Facebook, DateTimeBeginLicence = @DateTimeBeginLicence, DateTimeEndLicence = @DateTimeEndLicence, HasLicenceKey = @HasLicenceKey, Version = @Version, IsUse = @IsUse WHERE (ID = @ID)";
	        _SQLUpdate = query;

            query = @"INSERT INTO DM_BRANCH VALUES (@ID,@Branch_Name, @Branch_Address, @Branch_Phone, @Branch_Fax, @Branch_Email, @Branch_Note, @Branch_Logo, @Branch_Image, @Branch_Director, @Branch_Revenue, @Branch_Facebook, @DateTimeBeginLicence, @DateTimeEndLicence, @HasLicenceKey, @Version, @IsUse)";
	        _SQLInsert = query;

            query = @"DELETE FROM DM_BRANCH WHERE (ID = @ID)";
	        _SQLDelete = query;

            query = @"SELECT ID, IDPOS, Branch_Name, Branch_Address, Branch_Phone, Branch_Fax, Branch_Email, Branch_Note, Branch_Logo, Branch_Image, Branch_Director, Branch_Revenue, Branch_Facebook, DateTimeBeginLicence, DateTimeEndLicence, HasLicenceKey, Version, IsUse FROM DM_BRANCH";
	        _SQLSelectAll = query;
        }
        #endregion
        public void LoadByCommandText(string commandtext, IDbTransaction trans)
        {
            if (_dbConnection == null)
            {
                throw new Exception("Connection has not been set.");
            }

            IDbCommand command;

            try
            {
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }

                command = _dbConnection.CreateCommand();
                command.CommandText = commandtext;

				if (trans != null) { command.Transaction = trans; }
                if (command.ExecuteNonQuery() == 0) { throw new Exception("Zero rows were affected."); }
            }
            catch (Exception ex) { throw ex; }
        }

        #region Save & Delete
        private void Save(DM_BRANCH obj, IDbTransaction trans, bool isExisting)
        {
            if (_dbConnection == null) { throw new Exception("Connection has not been set."); }

            IDbCommand command;

            try
            {
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }
                command = _dbConnection.CreateCommand();

                if (!isExisting) { command.CommandText = _SQLInsert + ";"; }
                else { command.CommandText = _SQLUpdate + ";"; }

                         DalTools.addDbDataParameter(command, "ID", obj.ID, DbType.String);
                         DalTools.addDbDataParameter(command, "IDPOS", obj.IDPOS, DbType.Int32);
         DalTools.addDbDataParameter(command, "Branch_Name", obj.Branch_Name, DbType.String);
         DalTools.addDbDataParameter(command, "Branch_Address", obj.Branch_Address, DbType.String);
         DalTools.addDbDataParameter(command, "Branch_Phone", obj.Branch_Phone, DbType.String);
         DalTools.addDbDataParameter(command, "Branch_Fax", obj.Branch_Fax, DbType.String);
         DalTools.addDbDataParameter(command, "Branch_Email", obj.Branch_Email, DbType.String);
         DalTools.addDbDataParameter(command, "Branch_Note", obj.Branch_Note, DbType.String);
         DalTools.addDbDataParameter(command, "Branch_Logo", obj.Branch_Logo, DbType.Binary);
         DalTools.addDbDataParameter(command, "Branch_Image", obj.Branch_Image, DbType.Binary);
         DalTools.addDbDataParameter(command, "Branch_Director", obj.Branch_Director, DbType.String);
         DalTools.addDbDataParameter(command, "Branch_Revenue", obj.Branch_Revenue, DbType.Decimal);
         DalTools.addDbDataParameter(command, "Branch_Facebook", obj.Branch_Facebook, DbType.String);
         DalTools.addDbDataParameter(command, "DateTimeBeginLicence", obj.DateTimeBeginLicence, DbType.DateTime);
         DalTools.addDbDataParameter(command, "DateTimeEndLicence", obj.DateTimeEndLicence, DbType.DateTime);
         DalTools.addDbDataParameter(command, "HasLicenceKey", obj.HasLicenceKey, DbType.Boolean);
         DalTools.addDbDataParameter(command, "Version", obj.Version, DbType.String);
         DalTools.addDbDataParameter(command, "IsUse", obj.IsUse, DbType.Boolean);

		        if (trans != null) { command.Transaction = trans; }
                if (command.ExecuteNonQuery() == 0) { throw new Exception("Zero rows were affected."); }
            }
            catch (Exception ex) { throw ex; }
        }

        public void Delete(DM_BRANCH obj, IDbTransaction trans)
        {
            if (_dbConnection == null)
            {
                //throw new DalException("Connection has not been set.");
                throw new Exception("Connection has not been set.");
            }

            IDbCommand command;

            try
            {
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }

                command = _dbConnection.CreateCommand();
                command.CommandText = _SQLDelete + ";";

                         DalTools.addDbDataParameter(command, "ID", obj.ID, DbType.String);

		        if (trans != null) { command.Transaction = trans; }
                if (command.ExecuteNonQuery() == 0) { throw new Exception("Zero rows were affected."); }
            }
            catch (Exception ex) { throw ex; }
        }

        public void Insert(DM_BRANCH obj, IDbTransaction trans)
        {
            Save(obj, trans, false);
        }
        public void Update(DM_BRANCH obj, IDbTransaction trans)
        {
            Save(obj, trans, true);
        }
        #endregion

        #region Command Execute & Read Properties
     
        private DM_BRANCHList SelectListByCommand(IDbCommand command)
        {
            DM_BRANCHList list = new DM_BRANCHList();
            if (_dbConnection == null) { throw new Exception("Connection has not been set."); }
            try
            {	
		DM_BRANCH obj;
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }
                using (IDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read()) 
					{ 
						obj = new DM_BRANCH();
						ReadProperties(obj, reader);
						list.Add(obj); 
					}
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }
		
        private DM_BRANCHList SelectListByCommand(IDbCommand command, IDbTransaction trans)
        {
            DM_BRANCHList list = new DM_BRANCHList();
            if (_dbConnection == null) { throw new Exception("Connection has not been set."); }
            try
            {	
				DM_BRANCH obj;
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }
				command.Transaction = trans;
                using (IDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read()) 
					{ 
						obj = new DM_BRANCH();
						ReadProperties(obj, reader);
						list.Add(obj); 
					}
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }

        private void DeleteListByCommand(IDbCommand command, IDbTransaction trans)
        {
            if (_dbConnection == null) { throw new Exception("Connection has not been set."); }
            try
            {
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }
                if (trans != null) { command.Transaction = trans; }
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
	
        private void ReadProperties(DM_BRANCH obj, IDataReader reader)
        {
            try
            {
                                if(!(reader["ID"] is DBNull))
		{
			obj.ID = (string)reader["ID"];
		}
                if(!(reader["IDPOS"] is DBNull))
		{
			obj.IDPOS = (Int32)reader["IDPOS"];
		}
                if(!(reader["Branch_Name"] is DBNull))
		{
			obj.Branch_Name = (string)reader["Branch_Name"];
		}
                if(!(reader["Branch_Address"] is DBNull))
		{
			obj.Branch_Address = (string)reader["Branch_Address"];
		}
                if(!(reader["Branch_Phone"] is DBNull))
		{
			obj.Branch_Phone = (string)reader["Branch_Phone"];
		}
                if(!(reader["Branch_Fax"] is DBNull))
		{
			obj.Branch_Fax = (string)reader["Branch_Fax"];
		}
                if(!(reader["Branch_Email"] is DBNull))
		{
			obj.Branch_Email = (string)reader["Branch_Email"];
		}
                if(!(reader["Branch_Note"] is DBNull))
		{
			obj.Branch_Note = (string)reader["Branch_Note"];
		}
                if(!(reader["Branch_Logo"] is DBNull))
		{
			obj.Branch_Logo = (byte[])reader["Branch_Logo"];
		}
                if(!(reader["Branch_Image"] is DBNull))
		{
			obj.Branch_Image = (byte[])reader["Branch_Image"];
		}
                if(!(reader["Branch_Director"] is DBNull))
		{
			obj.Branch_Director = (string)reader["Branch_Director"];
		}
                if(!(reader["Branch_Revenue"] is DBNull))
		{
			obj.Branch_Revenue = (decimal)reader["Branch_Revenue"];
		}
                if(!(reader["Branch_Facebook"] is DBNull))
		{
			obj.Branch_Facebook = (string)reader["Branch_Facebook"];
		}
                if(!(reader["DateTimeBeginLicence"] is DBNull))
		{
			obj.DateTimeBeginLicence = (DateTime)reader["DateTimeBeginLicence"];
		}
                if(!(reader["DateTimeEndLicence"] is DBNull))
		{
			obj.DateTimeEndLicence = (DateTime)reader["DateTimeEndLicence"];
		}
                if(!(reader["HasLicenceKey"] is DBNull))
		{
			obj.HasLicenceKey = (bool)reader["HasLicenceKey"];
		}
                if(!(reader["Version"] is DBNull))
		{
			obj.Version = (string)reader["Version"];
		}
                if(!(reader["IsUse"] is DBNull))
		{
			obj.IsUse = (bool)reader["IsUse"];
		}
            }
            catch (Exception ex)
            {
                throw new DalException("Failed to read properties from DataReader.", ex);
            }
        }
        #endregion
      
        public DM_BRANCH SelectByKey(string ID)
        {
            IDbCommand command  = _dbConnection.CreateCommand();
            command.CommandText = _SQLSelectByKey;
	                 DalTools.addDbDataParameter(command, "ID", ID, DbType.String);
      	    DM_BRANCHList list =  SelectListByCommand(command);

            if (list.Count == 0) { throw new Exception("No data was returned"); }
            return list[0];
        }

        public DM_BRANCH SelectByKey(string ID, IDbTransaction trans)
        {
            IDbCommand command  = _dbConnection.CreateCommand();
            command.CommandText = _SQLSelectByKey;
	                 DalTools.addDbDataParameter(command, "ID", ID, DbType.String);
      	    DM_BRANCHList list =  SelectListByCommand(command, trans);
			
            if (list.Count == 0) { throw new Exception("No data was returned"); }
            return list[0];
        }
		
        public DM_BRANCHList SelectAll()
        {
            IDbCommand command  = _dbConnection.CreateCommand();
            command.CommandText = _SQLSelectAll;
      	    DM_BRANCHList list =  SelectListByCommand(command);
	        return list;
        }
	public DM_BRANCHList SelectAll(IDbTransaction trans)
        {
            IDbCommand command  = _dbConnection.CreateCommand();
            command.CommandText = _SQLSelectAll;
      	    DM_BRANCHList list =  SelectListByCommand(command, trans);
	        return list;
        }
         public DM_BRANCHList SelectByCommand(string Command)
        {
            IDbCommand command  = _dbConnection.CreateCommand();
            command.CommandText = Command;
      	    DM_BRANCHList list =  SelectListByCommand(command);
	        return list;
        }

        public DM_BRANCHList SelectByCondition(DM_BRANCH obj, IDbTransaction trans)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            command.CommandText = "SELECT * FROM DM_BRANCH " + GetCondtionQuery(obj) + ";";
            FillParamToCommand(command, obj);
            DM_BRANCHList list = SelectListByCommand(command, trans);
            return list;
        }
		
		 public DM_BRANCHList SelectByCondition(DM_BRANCH obj)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            command.CommandText = "SELECT * FROM DM_BRANCH " + GetCondtionQuery(obj) + ";";
            FillParamToCommand(command, obj);
            DM_BRANCHList list = SelectListByCommand(command);
            return list;
        }
        private void DeleteByCondition(DM_BRANCH obj, IDbTransaction trans)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            command.CommandText = "DELETE FROM DM_BRANCH " + GetCondtionQuery(obj) + ";";
            FillParamToCommand(command, obj);
            DeleteListByCommand(command, trans);
        }

        #region Make Condition Query & Fill Param from Condition to Command
        private string GetCondtionQuery(DM_BRANCH obj)
        {
            if (obj == null) { return ""; }
            string s = "";
            	if (obj.IsSetID)
	{
    	    s += " AND ID = @ID ";
	}
	if (obj.IsSetIDPOS)
	{
    	    s += " AND IDPOS = @IDPOS ";
	}
	if (obj.IsSetBranch_Name)
	{
    	    s += " AND Branch_Name = @Branch_Name ";
	}
	if (obj.IsSetBranch_Address)
	{
    	    s += " AND Branch_Address = @Branch_Address ";
	}
	if (obj.IsSetBranch_Phone)
	{
    	    s += " AND Branch_Phone = @Branch_Phone ";
	}
	if (obj.IsSetBranch_Fax)
	{
    	    s += " AND Branch_Fax = @Branch_Fax ";
	}
	if (obj.IsSetBranch_Email)
	{
    	    s += " AND Branch_Email = @Branch_Email ";
	}
	if (obj.IsSetBranch_Note)
	{
    	    s += " AND Branch_Note = @Branch_Note ";
	}
	if (obj.IsSetBranch_Logo)
	{
    	    s += " AND Branch_Logo = @Branch_Logo ";
	}
	if (obj.IsSetBranch_Image)
	{
    	    s += " AND Branch_Image = @Branch_Image ";
	}
	if (obj.IsSetBranch_Director)
	{
    	    s += " AND Branch_Director = @Branch_Director ";
	}
	if (obj.IsSetBranch_Revenue)
	{
    	    s += " AND Branch_Revenue = @Branch_Revenue ";
	}
	if (obj.IsSetBranch_Facebook)
	{
    	    s += " AND Branch_Facebook = @Branch_Facebook ";
	}
	if (obj.IsSetDateTimeBeginLicence)
	{
    	    s += " AND DateTimeBeginLicence = @DateTimeBeginLicence ";
	}
	if (obj.IsSetDateTimeEndLicence)
	{
    	    s += " AND DateTimeEndLicence = @DateTimeEndLicence ";
	}
	if (obj.IsSetHasLicenceKey)
	{
    	    s += " AND HasLicenceKey = @HasLicenceKey ";
	}
	if (obj.IsSetVersion)
	{
    	    s += " AND Version = @Version ";
	}
	if (obj.IsSetIsUse)
	{
    	    s += " AND IsUse = @IsUse ";
	}
            if (s.Length > 0)
            {
                s =" WHERE "+s.Substring(4);
                return s;
            }
            return "";
        }

        private void FillParamToCommand(IDbCommand command, DM_BRANCH obj)
        {
	    if (obj == null) { return;}
            	            if (obj.IsSetID)
            {
            	DalTools.addDbDataParameter(command, "ID", obj.ID, DbType.String); 
            }
            if (obj.IsSetIDPOS)
            {
            	DalTools.addDbDataParameter(command, "IDPOS", obj.IDPOS, DbType.Int32); 
            }
            if (obj.IsSetBranch_Name)
            {
            	DalTools.addDbDataParameter(command, "Branch_Name", obj.Branch_Name, DbType.String); 
            }
            if (obj.IsSetBranch_Address)
            {
            	DalTools.addDbDataParameter(command, "Branch_Address", obj.Branch_Address, DbType.String); 
            }
            if (obj.IsSetBranch_Phone)
            {
            	DalTools.addDbDataParameter(command, "Branch_Phone", obj.Branch_Phone, DbType.String); 
            }
            if (obj.IsSetBranch_Fax)
            {
            	DalTools.addDbDataParameter(command, "Branch_Fax", obj.Branch_Fax, DbType.String); 
            }
            if (obj.IsSetBranch_Email)
            {
            	DalTools.addDbDataParameter(command, "Branch_Email", obj.Branch_Email, DbType.String); 
            }
            if (obj.IsSetBranch_Note)
            {
            	DalTools.addDbDataParameter(command, "Branch_Note", obj.Branch_Note, DbType.String); 
            }
            if (obj.IsSetBranch_Logo)
            {
            	DalTools.addDbDataParameter(command, "Branch_Logo", obj.Branch_Logo, DbType.Binary); 
            }
            if (obj.IsSetBranch_Image)
            {
            	DalTools.addDbDataParameter(command, "Branch_Image", obj.Branch_Image, DbType.Binary); 
            }
            if (obj.IsSetBranch_Director)
            {
            	DalTools.addDbDataParameter(command, "Branch_Director", obj.Branch_Director, DbType.String); 
            }
            if (obj.IsSetBranch_Revenue)
            {
            	DalTools.addDbDataParameter(command, "Branch_Revenue", obj.Branch_Revenue, DbType.Decimal); 
            }
            if (obj.IsSetBranch_Facebook)
            {
            	DalTools.addDbDataParameter(command, "Branch_Facebook", obj.Branch_Facebook, DbType.String); 
            }
            if (obj.IsSetDateTimeBeginLicence)
            {
            	DalTools.addDbDataParameter(command, "DateTimeBeginLicence", obj.DateTimeBeginLicence, DbType.DateTime); 
            }
            if (obj.IsSetDateTimeEndLicence)
            {
            	DalTools.addDbDataParameter(command, "DateTimeEndLicence", obj.DateTimeEndLicence, DbType.DateTime); 
            }
            if (obj.IsSetHasLicenceKey)
            {
            	DalTools.addDbDataParameter(command, "HasLicenceKey", obj.HasLicenceKey, DbType.Boolean); 
            }
            if (obj.IsSetVersion)
            {
            	DalTools.addDbDataParameter(command, "Version", obj.Version, DbType.String); 
            }
            if (obj.IsSetIsUse)
            {
            	DalTools.addDbDataParameter(command, "IsUse", obj.IsUse, DbType.Boolean); 
            }
        }
        #endregion
    }
}